<?php 
/**
 * NOTICE OF LICENSE
 *
 * This source file is subject to the  Academic Free License (AFL 3.0)
 * that is available through the world-wide-web at this URL:
 * http://opensource.org/licenses/afl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to support@zprofess.com so we can send you a copy immediately.
 *
 * @category   	Prestashop module
 * @package		Help Desk
 * @version 	1.0
 * @author 		Kurosaki <kurosaki@zprofess.com>
 * @copyright   Copyright (c) 2012 ZPROFESS. (http://zprofess.com)
 * @license    	http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
 */

require_once(dirname(__FILE__).'/ZemailSend.php');
class ZticketObject extends ObjectModel
{
	/** @var string SQL Table name */
	protected 	$table ='zhelpdesk_ticket';
	/** @var string SQL Table identifier */
	protected 	$identifier = 'id_zhelpdesk_ticket';
	
	/** @var integer Object id */
    public      $id_zhelpdesk_ticket;  
    /** @var integer Object id department */
    public      $id_zhelpdesk_department;  
    /** @var varchar Object ticket code */   
	public      $ticket_code;
	/** @var tinytext Object email customer */
	public      $email_customer;
	/** @var tinytext Object name customer */
	public      $name_customer;
	/** @var integer Object staff */
	public 		$id_staff;
	/** @var tinytext Object subject */
	public      $subject;
	/** @var integer Object id order */
	public      $id_order;
	/** @var date time Object date_add */
	public      $date_add;
	/** @var date time Object date_upd */
	public      $date_upd;
	/** @var integer Object id_customer if not customer id_customer = 0 */
	public      $id_customer;
	/** @var integer Object id ticket status */
	public      $id_zhelpdesk_ticket_status;
	/** @var integer Object id ticket priovity */
	public      $id_zhelpdesk_ticket_priority;
	
	/** @var array Fields validity functions for admin panel forms */
	protected $fieldsValidate = array(
		'id_zhelpdesk_ticket' 	 			=> 'isInt',
		'id_zhelpdesk_department' 			=> 'isInt',
		'id_zhelpdesk_ticket_status'		=> 'isInt',
		'id_zhelpdesk_ticket_priority'		=> 'isInt',
//		'email_customer'					=> 'isEmail',
		
	);
	/**
	 * Build object
	 *
	 * @param integer $id Existing object id in order to load object (optional)
	 * @param integer $id_lang Required if object is multilingual (optional)
	 */
	public function  __construct($id = null, $lang = null)
	{
		parent::__construct($id, $lang);
	}
	/**
	 * @see ObjectModelCore::getFields()
	 * 
	 * Prepare fields for ObjectModel class (add, update)
	 * All fields are verified (pSQL, intval...)
	 *
	 * @return array All object fields
	 */
	public function getFields()
	{
		parent::validateFields();
		$fields['id_zhelpdesk_ticket'] = (int)($this->id_zhelpdesk_ticket);
		$fields['id_zhelpdesk_department'] = (int)($this->id_zhelpdesk_department);
		$fields['ticket_code'] = pSQL($this->ticket_code);
		$fields['email_customer'] = pSQL($this->email_customer);
		$fields['name_customer'] = pSQL($this->name_customer);
		$fields['id_staff'] = (int)($this->id_staff);
		$fields['subject'] = pSQL($this->subject);
		$fields['id_order'] = pSQL($this->id_order);
		$fields['date_add'] = ($this->date_add);
		$fields['date_upd'] = ($this->date_upd);
		$fields['id_customer'] = (int)($this->id_customer);
		$fields['id_zhelpdesk_ticket_status'] = (int)($this->id_zhelpdesk_ticket_status);
		$fields['id_zhelpdesk_ticket_priority'] = (int)($this->id_zhelpdesk_ticket_priority);
		return $fields;
	}
	/** 
	 * get All ticket 
	 * @param unknown_type $id_lang
	 * @param unknown_type $pageNumber
	 * @param unknown_type $nb
	 */
	public static function getAllTicket ($id_customer,$id_lang,$pageNumber=1,$nb=10,$orderBy='date_add',$orderWay='ASC') {
		if ($pageNumber>0) {
			$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT t.`id_zhelpdesk_ticket`,t.`ticket_code`,t.`subject`,d.`name` as department,t.id_staff,p.`priority`,s.`status`,t.date_add ,e.firstname as firstname_employee, e.lastname as lastname_employee
	,pa.text_color as text_color_priority, pa.backgroud_color as backgroud_color_priority, sa.backgroud_color as backgroud_color_status, sa.text_color as text_color_status
				FROM '._DB_PREFIX_.'zhelpdesk_ticket t
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_department_lang d ON (t.id_zhelpdesk_department=d.id_zhelpdesk_department AND d.id_lang='.$id_lang.')
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_priority_lang p ON (t.id_zhelpdesk_ticket_priority = p.id_zhelpdesk_ticket_priority AND p.id_lang='.$id_lang.' )
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_priority pa ON (pa.id_zhelpdesk_ticket_priority = t.id_zhelpdesk_ticket_priority)
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_status_lang s ON (t.id_zhelpdesk_ticket_status = s.id_zhelpdesk_ticket_status AND s.id_lang='.$id_lang.' )
				LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_status sa ON (t.id_zhelpdesk_ticket_status = sa.id_zhelpdesk_ticket_status)
				LEFT JOIN '._DB_PREFIX_.'employee e ON (e.id_employee=t.id_staff)
			WHERE t.id_customer = '.$id_customer.'
			ORDER BY `'.pSQL($orderBy).'` '.pSQL($orderWay).'
			LIMIT '.(int)(($pageNumber-1) * $nb).', '.(int)($pageNumber *$nb));
			foreach ($result as $key=>$value) {
				if (Configuration::get('PS_REWRITING_SETTINGS')) {
	//				$result[$key]['link'] = Zalbum::getLinkUrl($value['id_zgallery_album']);
				}
				else 
					$result[$key]['linkViewDetail'] = Tools::getShopDomain(true).__PS_BASE_URI__.'modules/zhelpdesk/zhelpdesk-detail-ticket.php?ticket='.$value['id_zhelpdesk_ticket'];
			}
			return $result;
		}
		else return array ();
	}
	/**
	 * genecode
	 * Create random code
	 */
	
	public static function genCode ($len) {
		$rs='';
	    $a_z = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	    for ($i=0;$i<$len;$i++) {
	    	$int = rand(0,25);
	    	$rand_letter = $a_z[$int];
	    	$rs.=$rand_letter;
	    } 
	    return $rs;
	}
	/**
	 * Test have $code, avoid duplication code
	 * @param unknown_type $code
	 */
	public static function testSameCode ($code) {
		$rs = Db::getInstance()->getRow('
			SELECT COUNT(ticket_code) as nb FROM ' . _DB_PREFIX_ .'zhelpdesk_ticket
			WHERE ticket_code="'.$code.'" ');
		return ($rs['nb']);
	}
	/**
	 * Get Number field ticket
	 */
	public static function getNumberTicket ($id_customer) {
		$rs = Db::getInstance()->getRow('SELECT COUNT(id_zhelpdesk_department) as nb FROM ' . _DB_PREFIX_ .'zhelpdesk_ticket WHERE id_customer='.$id_customer);
		return ($rs['nb']);
	}
	/**
	 * Get Number field message for ticket
	 */
	public static function getNumberMessageTicket ($id_ticket) {
		$rs = Db::getInstance()->getRow('SELECT COUNT(id_zhelpdesk_ticket) as nb FROM ps_zhelpdesk_ticket_content WHERE id_zhelpdesk_ticket='.$id_ticket);
		return ($rs['nb']);
	}
	/**
	 * Set message for ticket
	 */
	public function setMessageForTicket ($content,$file="",$staff=false) {
		if ($staff==false)
			$rs = Db::getInstance()->Execute ('INSERT INTO `'._DB_PREFIX_.'zhelpdesk_ticket_content` (`id_zhelpdesk_ticket`, `content`,`date_add`) 
												VALUES (\''.$this->id.'\', \''.$content.'\',"'.date('Y-m-d H:i:s').'")');
		else 
			$rs = Db::getInstance()->Execute ('INSERT INTO `'._DB_PREFIX_.'zhelpdesk_ticket_content` (`id_zhelpdesk_ticket`, `content`,`date_add`,`id_staff`) 
												VALUES (\''.$this->id.'\', \''.$content.'\',"'.date('Y-m-d H:i:s').'",'.$this->id_staff.')');
		if ($rs && $file!="") {
			$id_content = Db::getInstance()->Insert_ID ();
			$img_ext = substr($file, strripos($file, '.')); // strip name
			$file = 'attach_'.$this->id.'_'.$id_content.$img_ext;
			$target = dirname(__FILE__).'/../fileattach/'.$file;
			if(move_uploaded_file($_FILES['attached_files']['tmp_name'],$target)==true) {
				return Db::getInstance()->Execute ('
					UPDATE `'._DB_PREFIX_.'zhelpdesk_ticket_content` SET `attached_files`="'.$file.'" WHERE (`id_zhelpdesk_ticket_content`=\''.$id_content.'\') AND (`id_zhelpdesk_ticket`=\''.$this->id.'\')
				');
			} else 
				return false;
		}
		else return $rs;
	}
	/**
	 * Get Message for ticket
	 * @param $pageNumber
	 * @param $nb
	 * @param $orderBy
	 * @param $orderWay
	 */
	public static function getAllMessageForTicket ($id_ticket,$pageNumber=1,$nb=10,$orderBy='date_add',$orderWay='DESC') {
		$rs = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
		SELECT tc.*,t.name_customer,e.firstname as firstname_employee, e.lastname as lastname_employee,t.id_customer FROM '._DB_PREFIX_.'zhelpdesk_ticket_content tc
		LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket t ON (t.id_zhelpdesk_ticket = tc.id_zhelpdesk_ticket)
		LEFT JOIN '._DB_PREFIX_.'employee e ON (e.id_employee=tc.id_staff)
		WHERE tc.id_zhelpdesk_ticket='.$id_ticket.'
		ORDER BY tc.`'.pSQL($orderBy).'` '.pSQL($orderWay).'
		LIMIT '.(int)(($pageNumber-1) * $nb).', '.(int)($pageNumber *$nb));
		foreach ($rs as $key=>$value) {
			if ($value['attached_files']!="" || $value['attached_files']!=null)
				$rs[$key]['link_attached_files'] = Tools::getShopDomain(true).__PS_BASE_URI__.'modules/zhelpdesk/fileattach/'.$value['attached_files'];
//			$rs[$key]['avg_rating'] = self::getAvgForContent ($rs[$key]['id_zhelpdesk_ticket_content']); 
		}
		return $rs;
	}
	
	/**
	 * 
	 */
	public static function getInfoTicket ($id_tiket,$id_lang){
		return Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
			SELECT t.ticket_code,t.id_order,pl.priority,p.backgroud_color as priority_background,p.text_color as priority_color ,t.`subject`
			,sl.`status` as `status`,s.backgroud_color as status_background,s.text_color as status_color,dl.`name` as department, e.`firstname` as firstname_employee, e.`lastname` as lastname_employee, t.id_staff
			FROM '._DB_PREFIX_.'zhelpdesk_ticket t
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_priority p ON (t.id_zhelpdesk_ticket_priority=p.id_zhelpdesk_ticket_priority)
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_status s ON (t.id_zhelpdesk_ticket_status=s.id_zhelpdesk_ticket_status)
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_priority_lang pl ON (t.id_zhelpdesk_ticket_priority=pl.id_zhelpdesk_ticket_priority AND pl.id_lang='.$id_lang.')
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_status_lang sl ON (t.id_zhelpdesk_ticket_status=sl.id_zhelpdesk_ticket_status AND sl.id_lang='.$id_lang.')
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_department_lang dl ON (t.id_zhelpdesk_department = dl.id_zhelpdesk_department AND dl.id_lang='.$id_lang.')
			LEFT JOIN '._DB_PREFIX_.'employee e ON (e.id_employee = t.id_staff)
			WHERE t.id_zhelpdesk_ticket='.$id_tiket
		);
	}
	
	/**
	 * 
	 * Return all content ticket
	 * @param integer $id id ticket
	 * 
	 * @return array
	 */
	public static function getContenTicket($id)
	{
		global $cookie;
		$values = Db::getInstance()->ExecuteS(' 
			SELECT *, zt.`id_staff` as `ticket_staff`, ztc.`id_staff` as `content_staff`
			FROM '._DB_PREFIX_.'zhelpdesk_ticket zt 
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_content ztc ON (zt.`id_zhelpdesk_ticket` = ztc.`id_zhelpdesk_ticket`)
			WHERE zt.id_zhelpdesk_ticket = '.$id.'
			ORDER BY ztc.`date_add` DESC');
		return $values;
	}
	/**
	 * Get avg rating for content
	 */
	public static function getAvgForContent ($id_content) {
		global $cookie;
		$rs = Db::getInstance()->getRow('
			SELECT AVG(tr.`value`) as avg FROM '._DB_PREFIX_.'zhelpdesk_ticket_content tc
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_rating tr ON (tc.id_zhelpdesk_ticket_rating = tr.id_zhelpdesk_ticket_rating)
			WHERE tc.id_zhelpdesk_ticket_rating!=0 AND tc.id_zhelpdesk_ticket_content='.$id_content.'
		');
		$value = (int)$rs['avg'];
		if ($rs['avg'] >= 2.5)
			$value++;
		$result = Db::getInstance()->getRow('
			SELECT tsl.rating FROM '._DB_PREFIX_.'zhelpdesk_ticket_rating ts 
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_ticket_rating_lang tsl ON (ts.id_zhelpdesk_ticket_rating=tsl.id_zhelpdesk_ticket_rating AND tsl.id_lang='.$cookie->id_lang.')
			WHERE ts.`value` <= '.$value.'
			ORDER BY ts.`value` DESC
		');
		return $result['rating']; 
	}
        
    /**
     *set status for ticket
     * 
     * @param integer $idTicket id ticket update status
     * @param integer $idStt id status
     * @return bool 
     */
     public static function setTicketStatus($idTicket, $idStt)
     {
     	$result = Db::getInstance()->Execute('
        	UPDATE `'._DB_PREFIX_.'zhelpdesk_ticket`
            SET `id_zhelpdesk_ticket_status` = '.(int)$idStt.'
            WHERE `id_zhelpdesk_ticket` = '.(int)$idTicket);
        return $result;
     }
     
     /**
      * 
      * update time ticket
      * @param integer $idTicket id ticket update time
      * @param datetime $date_upd
      * 
      * @return bool
      */
     public static function updateTicketTime($idTicket, $date_upd)
     {
     	$result = Db::getInstance()->Execute('
        	UPDATE `'._DB_PREFIX_.'zhelpdesk_ticket`
            SET `date_upd` = "'.$date_upd.'"
            WHERE `id_zhelpdesk_ticket` = '.(int)$idTicket);
        return $result;
     }
     
     /**
      * 
      * Filter ticket by status not close
      * 
      * @return array
      */
     public static function getTicketFilterClose()
     {
		$values = Db::getInstance()->ExecuteS(' 
			SELECT zt.*, e.`email`, e.`lastname`, e.`firstname`, zd.`id_moderator`
			FROM '._DB_PREFIX_.'zhelpdesk_ticket zt 
			LEFT JOIN '._DB_PREFIX_.'employee e ON (e.`id_employee` = zt.`id_staff`)
			LEFT JOIN '._DB_PREFIX_.'zhelpdesk_department zd ON (zd.`id_zhelpdesk_department` = zt.`id_zhelpdesk_department`)
			WHERE zt.`id_zhelpdesk_ticket_status` != 2
			');
		return $values;
     }
	/**
	 * 
	 * @param $email to email 
	 * @param $name  send by nam
	 * @param $subject subject letter
	 * @param $textbody content letter
	 * @param $words tpl var
	 */
	public static function sendMailTo ($email,$name,$subject,$textbody,$words) {
		$path=_PS_ROOT_DIR_."/modules/zhelpdesk/mails/"; 
		
		foreach ($words as $word=>$value) {
			$textbody = preg_replace ("/" . preg_quote($word) . "/",$value,$textbody);
			$textbody = str_replace($word, $value, $textbody);
		}
		$varsTpl = array(
			'{content}'=>$textbody,
		);
		
		if (Configuration::get('PS_MAIL_TYPE')==1)
			$textbody = pSQL($textbody);
//		return @Mail::Send('1', 'templateEmail', $subject , $varsTpl, $email ,$name,Configuration::get('ZHELPDESK_USER'),'help desk',NULL,NULL,$path,false);
		return @ZemailSend::Send('1', 'templateEmail', $subject , $varsTpl, $email ,$name,NULL,NULL,NULL,NULL,$path,false);
	}
	/**
	 * get ticket for subject
	 * @param unknown_type $subject
	 */
	public static function getTicketForSubject ($subject) {
		$rs = Db::getInstance()->getRow ('SELECT id_zhelpdesk_ticket FROM '._DB_PREFIX_.'zhelpdesk_ticket
											WHERE `subject`="'.$subject.'"');
		
		if ($rs==null){
			return null;
		} else 
			return new ZticketObject($rs['id_zhelpdesk_ticket']);
	}
	/**
	 * set file attachMent 
	 */
	public function setFileAttachmentForTicket ($file) {
		$id_content = Db::getInstance()->Insert_ID ();
		return Db::getInstance()->Execute ('
			UPDATE `'._DB_PREFIX_.'zhelpdesk_ticket_content` SET `attached_files`="'.$file.'" WHERE (`id_zhelpdesk_ticket_content`=\''.$id_content.'\') AND (`id_zhelpdesk_ticket`=\''.$this->id.'\')
		');
	}
}
?>